﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_NOE_Baidu]
	(
		@CompanyId int,
		--@DepId int,
		@StateId int,
		@TypeId int,
		--@WayId int,
		--@ProcessId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@NOE_Flag int,
		@DateType int
	)
As
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	--Declare @bmbh_T  varchar(30)
	--Select @bmbh_T=bmbh From department Where id=@DepId

If @DateType=1	--完结时间
	Begin
		;WITH list As(Select ROW_NUMBER() OVER (Order By FinishDate Desc,TradeDate Desc,id Desc)AS Row
			,Id
			,OId
			,OrderId
			,CusId
			,CusName
			,CusPerson
			,CusPersonTel
			,CusDescription
			,CusEstimate
			,TypeId
			,TypeName
			,WayId
			,WayName
			,ProcessId
			,ProcessName
			,Salesman
			,SalesmanAccount
			,IsNull(FinishDate,'1900-01-01') As TradeDate
			,CreateDate
			,YingShouPrice
			,IsNull(ShiShouPrice,0) As ShiShouPrice
			,Discount
			,PaymentTypeId
			,PaymentType
			,BankId
			,Bank
			,PaymentDescription
			,OtherDescription
			,DepId
			,CompanyId
			,StateId
			,IsNull((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
			,NOE_Flag
			,ISNULL((SELECT SUM(Project.SalePrice-Project.CostPrice) FROM Project WHERE Project.OrderId=[order].OId),0)AS MaoLi
			From [Order]
			Where (@CompanyId=0 Or CompanyId=@CompanyId) --And (@DepId=0 Or DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
			And (@TypeId=0 Or TypeId=@TypeId) --And (@WayId=0 Or WayId=@WayId) And (@ProcessId=0 Or ProcessId=@ProcessId)
			And FinishDate>=@sRq And FinishDate<=@eRq
			And (@Stext='' Or CusName like '%'+@Stext+'%' Or Salesman like '%'+@Stext+'%')
			And ((@NOE_Flag=100 And NOE_Flag<>1) Or NOE_Flag=@NOE_Flag)
		)
		Select *,(Select Count(0) From list) As RecordCount
			,(SELECT SUM(ShiShouPrice) FROM list) AS ShiShouSUM
			,(SELECT SUM(YingShouPrice) FROM list) AS YingShouSUM
			,(SELECT SUM (MaoLi) FROM list )AS MaoLiSUM
		From list Where Row Between @StartIndex and @EndIndex Order By Row
	End
Else	--报备时间
	Begin
		;WITH list As(Select ROW_NUMBER() OVER (Order By CreateDate Desc,TradeDate Desc,id Desc)AS Row
			,Id
			,OId
			,OrderId
			,CusId
			,CusName
			,CusPerson
			,CusPersonTel
			,CusDescription
			,CusEstimate
			,TypeId
			,TypeName
			,WayId
			,WayName
			,ProcessId
			,ProcessName
			,Salesman
			,SalesmanAccount
			,IsNull(FinishDate,'1900-01-01') As TradeDate
			,CreateDate
			,YingShouPrice
			,IsNull(ShiShouPrice,0) As ShiShouPrice
			,Discount
			,PaymentTypeId
			,PaymentType
			,BankId
			,Bank
			,PaymentDescription
			,OtherDescription
			,DepId
			,CompanyId
			,StateId
			,IsNull((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
			,NOE_Flag
			,ISNULL((SELECT SUM(Project.SalePrice-Project.CostPrice) FROM Project WHERE Project.OrderId=[order].OId),0)AS MaoLi
			From [Order]
			Where (@CompanyId=0 Or CompanyId=@CompanyId) --And (@DepId=0 Or DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
			And (@StateId=0 Or StateId=@StateId) And (@TypeId=0 Or TypeId=@TypeId) --And (@WayId=0 Or WayId=@WayId) And (@ProcessId=0 Or ProcessId=@ProcessId)
			And CreateDate>=@sRq And CreateDate<=@eRq
			And (@Stext='' Or CusName like '%'+@Stext+'%' Or Salesman like '%'+@Stext+'%')
			And ((@NOE_Flag=100 And NOE_Flag<>1) Or NOE_Flag=@NOE_Flag)
		)
		
		Select *,(Select Count(0) From list) As RecordCount 
			,(SELECT SUM(ShiShouPrice) FROM list) AS ShiShouSUM
			,(SELECT SUM(YingShouPrice) FROM list) AS YingShouSUM
			,(SELECT SUM (MaoLi) FROM list )AS MaoLiSUM
		From list Where Row Between @StartIndex and @EndIndex Order By Row
	End
End
